﻿using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Data.SqlClient;

namespace AutomaticScrewLocking.infrastructure
{
    public class SqlHelper
    {
        // mysql连接数据库url
        private static string url = @"Database='auto_screw_locking';Data Source='localhost';User Id='root';Password='123456';charset='utf8';pooling=true;oldsyntax=true";

        #region 执行查询语句，返回MySqlDataReader
        /// <summary>
        /// 执行查询语句，返回MySqlDataReader
        /// </summary>
        /// <param name="sqlstring"></param>
        /// <returns></returns>
        public static MySqlDataReader executeReader(string sqlstring)
        {
            // 数据库连接管理器
            MySqlConnection connection = new MySqlConnection(url);
            // SQL 命令执行器
            MySqlCommand cmd = new MySqlCommand(sqlstring, connection);
            // Mysql的数据读取器
            MySqlDataReader myReader = null;
            try
            {
                connection.Open();
                // 执行查询，指定关闭Reader时自动关闭连接
                myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (SqlException ex)
            {
                connection.Close();
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
            }
        }
        #endregion


        #region 执行带参数的查询语句，返回MySqlDataReader
        /// <summary>
        /// 执行带参数的查询语句，返回MySqlDataReader
        /// </summary>
        /// <param name="sqlstring"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static MySqlDataReader executeReader(string sqlstring, params MySqlParameter[] cmdParms)
        {
            // 数据库连接管理器
            MySqlConnection connection = new MySqlConnection(url);
            // SQL 命令执行器
            MySqlCommand cmd = new MySqlCommand();
            // Mysql的数据读取器
            MySqlDataReader myReader = null;
            try
            {
                prepareCommand(cmd, connection, null, sqlstring, cmdParms);
                myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (SqlException ex)
            {
                connection.Close();
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
            }
        }
        #endregion

        #region 执行sql语句,返回执行行数
        /// <summary>
        /// 执行sql语句,返回执行行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static int executeSql(string sqlstring)
        {
            using (MySqlConnection connection = new MySqlConnection(url))
            using (MySqlCommand cmd = new MySqlCommand(sqlstring, connection))
            {
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        #endregion

        #region 执行带参数的sql语句，并返回执行行数
        /// <summary>
        /// 执行带参数的sql语句，并返回执行行数
        /// </summary>
        /// <param name="sqlstring"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static int executeSql(string sqlstring, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection connection = new MySqlConnection(url))
            using (MySqlCommand cmd = new MySqlCommand())
            {
                try
                {
                    prepareCommand(cmd, connection, null, sqlstring, cmdParms);
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        #endregion

        #region 执行查询语句，返回DataSet
        /// <summary>
        /// 行查询语句，返回DataSet
        /// </summary>
        /// <param name="sqlstring"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static DataSet getDataSet(string sqlstring)
        {
            using (MySqlConnection connection = new MySqlConnection(url))
            using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(sqlstring, connection))
            {
                DataSet dataSet = new DataSet();
                try
                {
                    connection.Open();
                    dataAdapter.Fill(dataSet);
                    return dataSet;
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        #endregion


        #region 执行带参数的查询语句，返回DataSet
        /// <summary>
        /// 执行带参数的查询语句，返回DataSet
        /// </summary>
        /// <param name="sqlstring"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static DataSet getDataSet(string sqlstring, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection connection = new MySqlConnection(url))
            using (MySqlCommand cmd = new MySqlCommand())
            using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd))
            {
                DataSet dataSet = new DataSet();
                try
                {
                    prepareCommand(cmd, connection, null, sqlstring, cmdParms);
                    dataAdapter.Fill(dataSet, "dataSet");
                    return dataSet;
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                
            }
        }
        #endregion

        #region 执行带参数的sql语句，并返回object
        /// <summary>
        /// 执行带参数的sql语句，并返回object
        /// </summary>
        /// <param name="sqlstring"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static object getSingle(string sqlstring, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection connection = new MySqlConnection(url))
            using (MySqlCommand cmd = new MySqlCommand())
            {
                try
                {
                    prepareCommand(cmd, connection, null, sqlstring, cmdParms);
                    object obj = cmd.ExecuteScalar();
                    // 处理空结果（DBNull.Value 转换为 null）
                    return obj == DBNull.Value ? null : obj;
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        #endregion

        #region 执行存储过程,返回数据集
        /// <summary>
        /// 执行存储过程,返回数据集
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>DataSet</returns>
        public static DataSet runProcedureForDataSet(string storedProcName, IDataParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(url))
            using (MySqlDataAdapter sqlDA = new MySqlDataAdapter())
            {
                try
                {
                    DataSet dataSet = new DataSet();
                    connection.Open();
                    sqlDA.SelectCommand = buildQueryCommand(connection, storedProcName, parameters);
                    sqlDA.Fill(dataSet);
                    return dataSet;
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        #endregion


        #region 批量插入数据库
        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName">数据表</param>
        /// <param name="table">更新的数据</param>
        /// <returns></returns>
        /// <exception cref="ArgumentException"></exception>
        public static bool batchInsertData(string tableName, DataTable table)
        {

            if (string.IsNullOrWhiteSpace(tableName))
                throw new ArgumentException("目标表名不能为空！", nameof(tableName));

            if (table == null || table.Rows.Count == 0)
                throw new ArgumentException("待插入数据集不能为空或无数据！", nameof(table));

            bool isInsert = false;
            using(MySqlConnection connection = new MySqlConnection(url))
            {
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                MySqlCommandBuilder commandBulider = new MySqlCommandBuilder(adapter);
                commandBulider.ConflictOption = ConflictOption.OverwriteChanges;
                MySqlTransaction transaction = null;
                try
                {
                    connection.Open();
                    transaction = connection.BeginTransaction();
                    adapter.UpdateBatchSize = table.Rows.Count;         //设置每次批处理操作的个数     
                    adapter.SelectCommand.Transaction = transaction;
                    adapter.SelectCommand.CommandText = "select * from " + tableName; //用于分析结构
                    adapter.Update(table);                              //操作数据库
                    transaction.Commit();                               //提交事务
                    isInsert = true;
                }
                catch (MySqlException ex)
                {
                    if (transaction != null) transaction.Rollback();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
                return isInsert;
            }
        }
        #endregion

        #region 装载MySqlCommand对象
        /// <summary>
        /// 装载MySqlCommand对象
        /// </summary>
        /// <param name="cmd"> 命令对象 </param>
        /// <param name="connection"> 数据库连接对象 </param>
        /// <param name="trans"> 事务对象 </param>
        /// <param name="cmdText"> SQL执行语句 </param>
        /// <param name="cmdParms"> SQL 参数数组 </param>
        private static void prepareCommand(MySqlCommand cmd, MySqlConnection connection, MySqlTransaction trans, string cmdText,
            MySqlParameter[] cmdParms)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            cmd.Connection = connection;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = CommandType.Text; //cmdType;
            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }
        #endregion

        #region 构建 SqlCommand 对象
        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns></returns>
        private static MySqlCommand buildQueryCommand(MySqlConnection connection, string storedProcName,
                IDataParameter[] parameters)
        {
            MySqlCommand command = new MySqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (MySqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }
        #endregion

    }
}
